package controller;

import java.util.ArrayList;
import java.util.regex.Pattern;

import dto.ColumnDTO;
import dto.QueryParserDTO;

/**
 * Parsing query statement.
 * 
 * @author Stanley
 * 
 */
public class QueryParser {
	private String query;

	/**
	 * constructor with query
	 * 
	 * @param query
	 *            query statement
	 */
	public QueryParser(String query) {
		super();
		this.query = query;
	}

	/**
	 * Check query is create database statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isCreateDatabaseStatement() {
		String createDatabase = "\\s*(?i)create\\s+(?i)database\\s+\\w+\\s*";
		return this.query.matches(createDatabase);
	}

	public boolean isUseDatabaseStatement() {
		String useDatabase = "\\s*(?)use\\s+(\\w+|\\'\\w+\\')\\s*";
		return this.query.matches(useDatabase);
	}

	/**
	 * Check query is create table statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isCreateTableStatement() {
		String createTableCols = "\\(\\s*(\\w+\\s+\\w+)(\\s*,\\s*(\\w+\\s+\\w+))*\\s*\\)";
		String createTable = "\\s*(?i)create\\s+(?i)table\\s+\\w+\\s*"
				+ createTableCols;
		return this.query.matches(createTable);
	}

	/**
	 * Check query is delete statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isDeleteStatement() {
		String operators = "(=|!=|>|<|<=|>=|like|not like)";
		String and = "(\\s+(?i)and\\s+((\\w+|(-)?\\d+)\\s*" + operators
				+ "\\s*((\\'(\\%)*\\w+(\\%)*\\'|(-)?\\d+))))*";
		String or = "(\\s+(?i)or\\s+((\\w+|(-)?\\d+)\\s*" + operators
				+ "\\s*((\\'(\\%)*\\w+(\\%)*\\'|(-)?\\d+))))*";
		String where = "((\\s+(?i)where\\s+((\\w+|(-)?\\d+)\\s*" + operators
				+ "\\s*((\\'(\\%)*\\w+(\\s*\\w+\\s*)*(\\%)*\\'|(-)?\\d+))))*" + and
				+ or + ")*";
		
		String delete = "\\s*(?i)delete\\s+(?i)from\\s+\\w+\\s*" + where;

		return this.query.matches(delete);
	}

	/**
	 * Check query is drop database statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isDropDatabaseStatement() {
		String dropDatabase = "\\s*(?i)drop\\s+(?i)database\\s+\\w+\\s*";

		return this.query.matches(dropDatabase);
	}

	/**
	 * Check query is drop table statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isDropTableStatement() {
		String dropTable = "\\s*(?i)drop\\s+(?i)table\\s+\\w+\\s*";

		return this.query.matches(dropTable);
	}

	/**
	 * Check query is insert statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isInsertStatement() {
		String insertCols = "(\\s*\\(\\s*(\\w+)(\\s*,\\s*\\w+)*\\s*\\))?";
		String insertValues = "\\(\\s*((\\'\\w+(\\s*\\w+\\s*)*\\')|(-)?\\d+)(\\s*,\\s*((\\'\\w+(\\s*\\w+\\s*)*\\')|(-)?\\d+))*\\s*\\)\\s*";
		String insert = "\\s*(?i)insert\\s+(?i)into\\s+(\\w+)" + insertCols
				+ "\\s*(?i)values\\s*" + insertValues + "\\s*";

		return this.query.matches(insert);
	}

	/**
	 * Check query is select statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isSelectStatement() {
		String selectCols = "(?i)(\\*|(\\w+)(\\s*,\\s*(\\w+))*)";

		String operators = "(=|!=|>|<|<=|>=|like|not like)";
		String and = "(\\s+(?i)and\\s+((\\w+|(-)?\\d+)\\s*" + operators
				+ "\\s*((\\'(\\%)*\\w+(\\s*\\w*\\s*)*(\\%)*\\'|(-)?\\d+))))*";
		String or = "(\\s+(?i)or\\s+((\\w+|(-)?\\d+)\\s*" + operators
				+ "\\s*((\\'(\\%)*\\w+(\\s*\\w*\\s*)*(\\%)*\\'|(-)?\\d+))))*";
		String where = "((\\s+(?i)where\\s+((\\w+|(-)?\\d+)\\s*" + operators
				+ "\\s*((\\'(\\%)*\\w+(\\s*\\w+\\s*)*(\\%)*\\'|(-)?\\d+))))*" + and
				+ or + ")*";

		String groupby = "(\\s+(?i)(group\\s+by\\s+)(\\w+)(\\s*,\\s*\\w+)*)*";

		// String having = "(\\s+(?i)having)*";

		String joins = "(\\w+)\\s+(join|left join|right join)\\s+(\\w+)\\s+on\\s+(\\w+.\\w+)\\s*"
				+ operators + "\\s*\\w+.\\w+";

		String from = "\\s+(?i)from\\s+((\\w+)(\\s*,\\s*\\w+)*|" + joins + ")";

		String orderby = "(\\s+(?)order\\s+(?i)by\\s+(\\w+)(\\s*,\\s*\\w+)*)*";
		String select = "\\s*(?i)select\\s+" + selectCols + from + where
				+ groupby + orderby + "\\s*";

		return this.query.matches(select);
	}

	/**
	 * Check query is update statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isUpdateStatement() {
		String operators = "(=|!=|>|<|<=|>=|like|not like)";
		String and = "(\\s+(?i)and\\s+((\\w+|(-)?\\d+)\\s*" + operators
				+ "\\s*((\\'(\\%)*\\w+(\\%)*\\'|(-)?\\d+))))*";
		String or = "(\\s+(?i)or\\s+((\\w+|(-)?\\d+)\\s*" + operators
				+ "\\s*((\\'(\\%)*\\w+(\\%)*\\'|(-)?\\d+))))*";
		String where = "((\\s+(?i)where\\s+((\\w+|(-)?\\d+)\\s*" + operators
				+ "\\s*((\\'(\\%)*\\w+(\\s*\\w+\\s*)*(\\%)*\\'|(-)?\\d+))))*" + and
				+ or + ")*";
		String updateData = "\\w+\\s*=\\s*(\\'\\w+(\\s*\\w+\\s*)*\\'|(-)?\\d+)(\\s*,\\s*(\\w+\\s*=\\s*(\\'\\w+(\\s*\\w+\\s*)*\\'|(-)?\\d+)))*\\s*";
		String update = "\\s*(?i)update\\s+(\\w+|\\'\\w+\\')\\s+(?i)set\\s+"
				+ updateData + where;

		return this.query.matches(update);
	}

	/**
	 * parse query main method of QueryParser class
	 * 
	 * @return QueryParserDTO
	 * @throws Exception
	 */
	public QueryParserDTO parse() throws Exception {
		// create database
		if (isCreateDatabaseStatement()) {
			return parseCreateDatabaseQuery();
		}

		if (isUseDatabaseStatement()) {
			return parseUseDatabaseQuery();
		}

		// create table
		if (isCreateTableStatement()) {
			return parseCreateTableQuery();
		}

		// delete
		if (isDeleteStatement()) {
			return parseDeleteQuery();
		}

		// drop database
		if (isDropDatabaseStatement()) {
			return parseDropDatabaseQuery();
		}

		// drop table
		if (isDropTableStatement()) {
			return parseDropTableQuery();
		}

		// insert
		if (isInsertStatement()) {
			return parseInsertQuery();
		}

		// select
		if (isSelectStatement()) {
			return parseSelectQuery();
		}

		// update
		if (isUpdateStatement()) {
			return parseUpdateQuery();
		}
		return null;
	}

	/**
	 * Parse delete query
	 * @return
	 */
	private QueryParserDTO parseDeleteQuery() {
		Pattern p = Pattern.compile("(delete|from|where)",
				Pattern.CASE_INSENSITIVE);
		ArrayList<String> strings = QueryParser.removeAllEmptyString(p
				.split(query));

		ArrayList<Object> data = new ArrayList<>();
		if (strings.size() == 1) {
			// Delete table data
			// 1. Add table name
			data.add(strings.get(0));
		} else if (strings.size() == 2) {
			// Delete table data with where conditions
			// 1. Add table name
			data.add(strings.get(0));
			// 3. Add where conditions
			data.add(strings.get(1));
		}
		return new QueryParserDTO(EQueryType.DELETE, data);
	}

	/**
	 * Parse update query
	 * 
	 * @return QueryParserDTO
	 */
	private QueryParserDTO parseUpdateQuery() {
		Pattern p = Pattern.compile("(update|set|where)",
				Pattern.CASE_INSENSITIVE);
		ArrayList<String> strings = QueryParser.removeAllEmptyString(p
				.split(query));

		ArrayList<Object> data = new ArrayList<>();
		if (strings.size() == 2) {
			// Update and set, without where conditions
			// 1. Add table name
			data.add(strings.get(0));
			// 2. Add set statement
			data.add(strings.get(1));
		} else if (strings.size() == 3) {
			// Update, set with where conditions
			// 1. Add table name
			data.add(strings.get(0));
			// 2. Add set statement
			data.add(strings.get(1));
			// 3. Add where conditions
			data.add(strings.get(2));
		}
		return new QueryParserDTO(EQueryType.UPDATE, data);
	}

	/**
	 * Parse set database as default
	 * 
	 * @return QueryParserDTO
	 */
	private QueryParserDTO parseUseDatabaseQuery() {
		Pattern p = Pattern.compile("(use|\\')", Pattern.CASE_INSENSITIVE);

		ArrayList<String> strings = QueryParser.removeAllEmptyString(p
				.split(query));
		ArrayList<Object> data = new ArrayList<>(1);
		// Add database name into data
		data.add(strings.get(0));
		return new QueryParserDTO(EQueryType.USE_DATABASE, data);
	}

	/**
	 * Parse select statement.
	 * 
	 * @return
	 */
	private QueryParserDTO parseSelectQuery() {
		Pattern p = Pattern.compile("(select|from|where)",
				Pattern.CASE_INSENSITIVE);
		String[] strings = p.split(this.query);
		ArrayList<String> splits = removeAllEmptyString(strings);

		int selectType = splits.size();

		ArrayList<Object> data = new ArrayList<Object>();
		switch (selectType) {
		case 2: // select * from table
			// Index 0 select command
			data.add(splits.get(0).trim());
			// index 1 : from command
			data.add(splits.get(1).trim());
			return new QueryParserDTO(EQueryType.SELECT, data);
		case 3:
			// Index 0 select command
			data.add(splits.get(0).trim());
			// index 1 : from command
			data.add(splits.get(1).trim());
			// index 2 : where command
			data.add(splits.get(2).trim());
			return new QueryParserDTO(EQueryType.SELECT, data);
		default:
			break;
		}

		return new QueryParserDTO(EQueryType.SELECT, data);
	}

	/**
	 * Parse insert query
	 * 
	 * @return QueryParserDTO
	 * @author Stanley
	 * @throws Exception
	 */
	private QueryParserDTO parseInsertQuery() throws Exception {
		Pattern p = Pattern.compile("(insert|into|values|\\(|\\))",
				Pattern.CASE_INSENSITIVE);
		String[] strings = p.split(query);
		ArrayList<String> splits = removeAllEmptyString(strings);
		ArrayList<Object> data = new ArrayList<>();

		// If columns didn't exists in query
		if (splits.size() == 2) {
			// Index = 0 : table name
			data.add(splits.get(0).trim());
			// Index = 1 : values array
			data.add(parseInsertValues(splits.get(1).trim()));

		} else if (splits.size() == 3) {
			// Index = 0 : table name
			data.add(splits.get(0).trim());
			// Index = 1 : columns
			p = Pattern.compile(",");
			ArrayList<String> columns = removeAllEmptyString(p.split(splits
					.get(1).trim()));
			data.add(columns);
			// Index = 2 : values array
			ArrayList<String> values = parseInsertValues(splits.get(2).trim());
			data.add(values);
			if (columns.size() != values.size()) {
				throw new Exception("Insert statement is invalid");
			}
		}

		return new QueryParserDTO(EQueryType.INSERT, data);
	}

	/**
	 * Parse insert values from values string
	 * 
	 * @param valuesString
	 * @return
	 */
	private ArrayList<String> parseInsertValues(String valuesString) {
		Pattern p = Pattern.compile("(\\'|,)");
		String[] strings = p.split(valuesString);
		ArrayList<String> values = removeAllEmptyString(strings);

		return values;
	}

	/**
	 * Remove all null or empty string in string array.
	 * 
	 * @param strings
	 *            String[]
	 * @return ArrayList<String>
	 * @author Stanley
	 */
	public static ArrayList<String> removeAllEmptyString(String[] strings) {
		ArrayList<String> splits = new ArrayList<String>();
		for (int i = 0; i < strings.length; i++) {
			if (!strings[i].trim().isEmpty()) {
				splits.add(strings[i].trim());
			}
		}
		return splits;
	}

	private QueryParserDTO parseDropTableQuery() {
		Pattern p = Pattern.compile("((table[^(-)?\\d+]))",
				Pattern.CASE_INSENSITIVE);

		String[] strings = p.split(query);
		ArrayList<Object> data = new ArrayList<>(1);
		// Add database name into data
		data.add(strings[1].trim());
		return new QueryParserDTO(EQueryType.DROP_TABLE, data);
	}

	/**
	 * parse create database query
	 * 
	 * @return
	 */
	private QueryParserDTO parseCreateDatabaseQuery() {
		Pattern p = Pattern.compile("(database)", Pattern.CASE_INSENSITIVE);

		String[] strings = p.split(query);
		ArrayList<Object> data = new ArrayList<>(1);
		// Add database name into data
		data.add(strings[1].trim());
		return new QueryParserDTO(EQueryType.CREATE_DATABSE, data);
	}

	/**
	 * parse create table query
	 * 
	 * @return
	 */
	private QueryParserDTO parseCreateTableQuery() {
		Pattern p = Pattern.compile("((table[^(-)?\\d+])|\\(|\\))",
				Pattern.CASE_INSENSITIVE);

		String[] strings = p.split(this.query);

		String tblName = strings[1].trim();
		String cols = strings[2].trim();

		// split columns
		ArrayList<ColumnDTO> columnsDTO = new ArrayList<ColumnDTO>();
		p = Pattern.compile(",");
		String[] colList = p.split(cols);
		for (String string : colList) {
			if (string != null) {
				p = Pattern.compile("\\s+");
				String[] c = p.split(string.trim());
				columnsDTO.add(new ColumnDTO(c[0].trim(), c[1].trim()));
			}
		}

		ArrayList<Object> data = new ArrayList<>();
		// Add table name into data
		data.add(tblName);
		// Add columns
		data.add(columnsDTO);
		return new QueryParserDTO(EQueryType.CREATE_TABLE, data);
	}

	/**
	 * parse drop database query
	 * 
	 * @return
	 */
	private QueryParserDTO parseDropDatabaseQuery() {
		Pattern p = Pattern.compile("(database)", Pattern.CASE_INSENSITIVE);

		String[] strings = p.split(query);
		ArrayList<Object> data = new ArrayList<>(1);
		// Add database name into data
		data.add(strings[1].trim());
		return new QueryParserDTO(EQueryType.DROP_DATABASE, data);
	}

}
